<?php
/**
 * 
 * 价格查询模块
 * @author Jackie Yang
 *
 */
class model_price extends model_weixin{
	
	/**
	 * @description 获取有效的产品大类
	 * @author jackie yang
	 * @param  $data  array
	 */
	public static function get_price_class_list() {
		$data = array();
		$sql = "select product_class_id,class_name from weixin_product_class where status=0 and delete_flag=0 order by product_class_id asc";
		try {
    		$binds = array();
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds);
    		$data = $st->fetchAll();
    	}catch(Exception $e){
            framework_static_function::write_log(__METHOD__.":".$e->getMessage(),'weixin_sql');
        }
        return $data;
	}

    /**
     *
     * 查询是否有满足条件的子类
     *
     */
    public function countChild($parent_id,$visible){
        $data = array();
        $sql = "select count(*) as num from weixin_product as p,weixin_formula as f where
                p.parent_id=:parent_id and
                f.big_class_id = p.parent_id and
                f.small_class_id = p.product_id and
                (f.visible='*_*' or f.visible like '%".$visible."%') and
                p.status=0 and p.delete_flag=0 ";

        try {
            $binds = array(':parent_id' => $parent_id);
            $st = self::DB()->dbh->prepare($sql);
            $st->execute($binds);
            $data = $st->fetch();


        }catch(Exception $e){
            framework_static_function::write_log(__METHOD__.":".$e->getMessage(),'weixin_sql');
        }
        return $data['num'];
    }


    /**
     *
     * 查询是否有满足条件的子类
     *
     */
    public function getChild($parent_id,$visible){
        $data = array();
        $sql = "select p.* from weixin_product as p,weixin_formula as f where
                p.parent_id=:parent_id and
                f.big_class_id = p.parent_id and
                f.small_class_id = p.product_id and
                (f.visible='*_*' or f.visible like '%".$visible."%') and
                p.status=0 and p.delete_flag=0 and f.delete_flag=0";

        try {
            $binds = array(':parent_id' => $parent_id);
            $st = self::DB()->dbh->prepare($sql);
            $st->execute($binds);
            $data = $st->fetchAll();

        }catch(Exception $e){
            framework_static_function::write_log(__METHOD__.":".$e->getMessage(),'weixin_sql');
        }
        return $data;
    }

    public function getChildName($id){
        $data = array();
        $sql = "select produce_name from weixin_product where product_id = ".$id;

        try {
            $binds = array();
            $st = self::DB()->dbh->prepare($sql);
            $st->execute($binds);
            $data = $st->fetch();

        }catch(Exception $e){
            framework_static_function::write_log(__METHOD__.":".$e->getMessage(),'weixin_sql');
        }
        return $data['produce_name'];
    }




	/**
	 * 
	 * 获取某一大类的小类
	 * @param string $parent_id 产品父类ID
	 */
	public static function get_small_class_list($parent_id) {
		$data = array();
		$sql = "select product_id,produce_name from weixin_product where parent_id=:parent_id and status=0 and delete_flag=0 order by product_id asc";
		try {
    		$binds = array(':parent_id' => $parent_id);
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds);
    		$data = $st->fetchAll();
    	}catch(Exception $e){
            framework_static_function::write_log(__METHOD__.":".$e->getMessage(),'weixin_sql');
        }
        return $data;
	}


	/**
	 * 获取产品材质信息
	 * @param string $produce_id 产品ID
	 */
	public static function get_marerials($produce_id){
		$data = array();
		if(empty($produce_id)){
			return $data;
		}
		$sql = "select basic_price_id,`marerial`,`markup` from `weixin_basic_price` where `produce_id` = :produce_id and `delete_flag` = 0 order by show_sort asc";
		try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute(array(":produce_id" => $produce_id));
    		$data = $st->fetchAll();			
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'weixin_sql');
    	}
		return $data;
	}
	/**
	 * 获取区域定价选项
	 * @param string $produce_id 产品ID
	 */
	public static function get_area_price($product_id){
		$data = array();
		if(empty($product_id)){
			return $data;
		}
		$sql = "select * from `weixin_area_price` where `produce_id` = :product_id and `delete_flag` = 0 order by sort asc";
		try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute(array(":product_id" => $product_id));
    		$data = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'weixin_sql');
    	}
		return $data;
	}
	/**
	 * 获取运送方式
	 * @param string $produce_id 产品ID
	 */
	public static function get_transport($product_id){
		$data = array();
		if(empty($product_id)){
			return $data;
		}
		$sql = "select  area_motor_id, area_smotor_id, area_ship_id, area_train_id from `weixin_product` where `product_id` = :product_id and `delete_flag` = 0";
		try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute(array(":product_id" => $product_id));
    		$data = $st->fetch();

            $result = array();
            $sql = 'select transport_tmp_id,tmp_name,tmp_type from weixin_transport_tmp where transport_tmp_id in(';
            //计算模板
            if($data['area_motor_id']){
                $sql .= $data['area_motor_id'].',';
            }
            if($data['area_smotor_id']){
                $sql .= $data['area_smotor_id'].',';
            }
            if($data['area_ship_id']){
                $sql .= $data['area_ship_id'].',';
            }
            if($data['area_train_id']){
                $sql .= $data['area_train_id'].',';
            }
            $sql .='0) and delete_flag = 0 ';

            $st = self::DB()->dbh->prepare($sql);
            $st->execute();
            $result = $st->fetchAll();

            //检查下面是否有设置运费
            if($result){
                foreach($result as $key =>$value){
                    $sql = 'select count(*) as num from weixin_transport_price where delete_flag = 0 and transport_tmp_id = '.$value['transport_tmp_id'];
                    $st = self::DB()->dbh->prepare($sql);
                    $st->execute();
                    $data = $st->fetch();
                    if($data['num'] <= 0){
                        unset($result[$key]);
                    }
                }
            }

    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'weixin_sql');
    	}
		return $result;
	}

    /**
     *
     * 获取某方式的到货地址
     *
     */
    public static function getArea($id){
        //weixin_transport_price  area_id
        if(empty($id)){
            return false;
        }
        //获取type
        $sql = 'select tmp_type from weixin_transport_tmp where transport_tmp_id = '.$id;
        $st = self::DB()->dbh->prepare($sql);
        $st->execute();
        $result = $st->fetch();
        $type = $result['tmp_type'];

        //1汽运 2火运 3水运 4水汽联运
        if($type == 1){
            $sql = "select DISTINCT p.area_id,aa.province,aa.city,a.*
                    from weixin_area_motor as a,weixin_transport_price as p,weixin_area as aa
                    where a.area_id = aa.area_id and p.area_id = a.area_motor_id and p.transport_tmp_id = ".$id." and p.transport_price > 0 and a.delete_flag = 0 and p.delete_flag = 0";
        }else if($type == 2){
            $sql = "select DISTINCT p.area_id,aa.province,aa.city,a.*
                    from weixin_area_train as a,weixin_transport_price as p,weixin_area as aa
                    where a.area_id = aa.area_id and p.area_id = a.area_train_id and p.transport_tmp_id = ".$id." and p.transport_price > 0 and a.delete_flag = 0 and p.delete_flag = 0";
        }else if($type == 3){
            $sql = "select DISTINCT p.area_id,aa.province,aa.city,a.*
                    from weixin_area_ship as a,weixin_transport_price as p,weixin_area as aa
                    where a.area_id = aa.area_id and p.area_id = a.area_ship_id and p.transport_tmp_id = ".$id." and p.transport_price > 0 and a.delete_flag = 0 and p.delete_flag = 0";
        }else if($type == 4){
            $sql = "select DISTINCT p.area_id,aa.province,aa.city,a.*
                    from weixin_area_smotor as a,weixin_transport_price as p,weixin_area as aa
                    where a.area_id = aa.area_id and p.area_id = a.area_smotor_id and p.transport_tmp_id = ".$id." and p.transport_price > 0  and a.delete_flag = 0 and p.delete_flag = 0";
        }


        try {
            $st = self::DB()->dbh->prepare($sql);
            $st->execute();
            $result = $st->fetchAll();

            //重构数据
            $data = array();
            foreach($result as $key => $value){
                if($type == 1){
                    $data[$key]['id'] = $value['area_motor_id'];
                    $data[$key]['name'] = $value['province'].'/'.$value['city'].'/'.$value['area'].'/'.$value['street'];
                }else if($type == 2){
                    $data[$key]['id'] = $value['area_train_id'];
                    $data[$key]['name'] = $value['province'].'/'.$value['city'].'/'.$value['area_station'];
                }else if($type == 3){
                    $data[$key]['id'] = $value['area_ship_id'];
                    $data[$key]['name'] = $value['province'].'/'.$value['city'].'/'.$value['port'].'/'.$value['port_dest'];
                }else if($type == 4){
                    $data[$key]['id'] = $value['area_smotor_id'];
                    $data[$key]['name'] = $value['province'].'/'.$value['city'].'/'.$value['area_wharf'];
                }
            }

        }catch(Exception $e){
            framework_static_function::write_log($e->getMessage(),'weixin_sql');
        }
        return $data;
    }

    /**
     * 获取自定义模块
     *
     */

    public static function getModel($small_class_id){
        if(empty($small_class_id)){
            return false;
        }
        $sql = "select * from weixin_formula where small_class_id = ".$small_class_id." and delete_flag = 0";
        $st = self::DB()->dbh->prepare($sql);
        $st->execute();
        $result = $st->fetch();
        $price_list = $result['price_list'];
        $price_list = str_replace('/',',',$price_list);
        if(empty($price_list)){
            return false;
        }

        //先查询第一级
        $sql = "select product_price_id,parent_id,price_name,depth,product_class_id,is_show,select_all
                from weixin_product_price where parent_id = 0 and product_price_id in (".$price_list.")";
        $sql.=' and delete_flag = 0 ';
        $st = self::DB()->dbh->prepare($sql);
        $st->execute();
        $data = $st->fetchAll();

        $result = array();
        foreach($data as $key=>$value){
            //检查是否有下级菜单
            $sql = "select product_price_id,parent_id,price_name,depth,product_class_id,is_show,select_all
                from weixin_product_price where parent_id = ".$value['product_price_id']." and product_price_id in (".$price_list.")";
            $sql.=' and delete_flag = 0 ';
            $st = self::DB()->dbh->prepare($sql);
            $st->execute();
            $oneList = $st->fetchAll();
            $value['childCount'] = count($oneList);
            if(count($oneList) >= 1){
                //检查2级是否有下级选项
                foreach($oneList as $kk=>$vv){
                    $sql = "select product_price_id,parent_id,price_name,depth,product_class_id,is_show,select_all
                            from weixin_product_price where parent_id = ".$vv['product_price_id']." and product_price_id in (".$price_list.")";
                    $sql.=' and delete_flag = 0 ';
                    $st = self::DB()->dbh->prepare($sql);
                    $st->execute();
                    $threeList = $st->fetchAll();
                    $oneList[$kk]['childCount'] = count($threeList);
                    if(count($threeList) >= 1){
                        $oneList[$kk]['child'] = $threeList;
                    }else{
                        if($value['select_all'] == 0){
                            unset($oneList[$kk]);
                        }
                    }
                }
                $value['child'] = $oneList;
                $result[] = $value;
                //child 更加if_show  切换顺序
            }
        }
        foreach($result as $key=>$value){
            if(count($value['child']) == 0){
                unset($result[$key]);
            }
        }

        return $result;
    }

	/**
	 * 获取某一字段的值
	 * @param int $produce_id 产品ID
	 * @param string $filed 字段
	 */
	public static function get_filed($product_id,$filed){
		$data = array();
		if(empty($product_id)){
			return $data;
		}
		$sql = "select {$filed} from `weixin_product` where `product_id` = :product_id and `delete_flag` = 0";
		try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute(array(":product_id" => $product_id));
    		$data = $st->fetchColumn();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'weixin_sql');
		}
		return $data;
	}
	/**
	 * 获取价格字段列表
	 */
	public static function get_price_list() {
		$sql = "select `product_price_id`,`parent_id`,`price_name`,`depth` from `weixin_product_price`  where `delete_flag` = 0 order by product_price_id asc ";
		$result = array ();
		try {
			$st = self::DB ()->dbh->prepare ($sql);
			$st->execute ();
			$result = $st->fetchAll ();
		} catch ( Exception $e ) {
			framework_static_function::write_log ( $e->getMessage (), 'weixin_sql' );
			$result = array ();
		}
		return $result;
    }
    /**
     * 根据传入参数，查询出价格
     * @param array $params
     */
    public static function calc_total_price($params) {
    	$result = '';
    	////////////////////////逻辑还没有写完
    	return $result;
    }


    public static function getModelResult($small_class_id){
        if(empty($small_class_id)){
            return false;
        }
        try {
            $sql = "select * from weixin_formula where small_class_id = ".$small_class_id." and delete_flag = 0";
            $st = self::DB()->dbh->prepare($sql);
            $st->execute();
            $result = $st->fetch();
            $price_list = $result['price_list'];
            $price_list = str_replace('/',',',$price_list);
            if(empty($price_list)){
                return false;
            }

            //先查询第一级
            $sql = "select product_price_id,parent_id,price_name,depth,product_class_id,is_show,select_all
                    from weixin_product_price where parent_id = 0 and product_price_id in (".$price_list.")";
            $sql.=' and is_show = 0';
            $sql.=' and delete_flag = 0 ';
            $st = self::DB()->dbh->prepare($sql);
            $st->execute();
            $data = $st->fetchAll();

            $result = array();
            foreach($data as $key=>$value){
                //检查是否有下级菜单
                $sql = "select product_price_id,parent_id,price_name,depth,product_class_id,is_show,select_all
                    from weixin_product_price where parent_id = ".$value['product_price_id']." and product_price_id in (".$price_list.")";
                $sql.=' and delete_flag = 0 ';
                $st = self::DB()->dbh->prepare($sql);
                $st->execute();
                $oneList = $st->fetchAll();
                $value['childCount'] = count($oneList);
                if(count($oneList) >= 1){
                    //检查2级是否有下级选项
                    foreach($oneList as $kk=>$vv){
                        $sql = "select product_price_id,parent_id,price_name,depth,product_class_id,is_show,select_all
                                from weixin_product_price where parent_id = ".$vv['product_price_id']." and product_price_id in (".$price_list.")";
                        $sql.=' and delete_flag = 0 ';
                        $st = self::DB()->dbh->prepare($sql);
                        $st->execute();
                        $threeList = $st->fetchAll();
                        $oneList[$kk]['childCount'] = count($threeList);
                        if(count($threeList) >= 1){
                            $oneList[$kk]['child'] = $threeList;
                        }else{
                            if($value['select_all'] == 0){
                                unset($oneList[$kk]);
                            }
                        }
                    }
                    $value['child'] = $oneList;
                    $result[] = $value;
                    //child 更加if_show  切换顺序
                }
            }
        } catch ( Exception $e ) {
            framework_static_function::write_log ( $e->getMessage (), 'weixin_sql' );
            $result = array ();
        }
        return $result;
    }


    /**
     * 获取快递费用
     * @param $type
     * @param $id
     * @param $number
     */
    public static function getTransportPrice($type,$id,$tmpId,$number){

        //var_dump($type.'--'.$id.'--'.$tmpId.'--'.$number);

        try {

            //1汽运 2火运 3水运 4水汽联运
            if($type == 1){
                $sql = "select p.*
                        from weixin_area_motor as a,weixin_transport_price as p
                        where p.area_id = a.area_motor_id and a.area_motor_id = ".$id." and p.transport_tmp_id = ".$tmpId."
                        and a.delete_flag = 0 and p.delete_flag = 0";
            }else if($type == 2){
                $sql = "select p.*
                        from weixin_area_train as a,weixin_transport_price as p
                        where p.area_id = a.area_train_id and a.area_train_id = ".$id." and p.transport_tmp_id = ".$tmpId."
                        and a.delete_flag = 0 and p.delete_flag = 0";
            }else if($type == 3){
                $sql = "select p.*
                        from weixin_area_ship as a,weixin_transport_price as p
                        where p.area_id = a.area_ship_id and a.area_ship_id = ".$id." and p.transport_tmp_id = ".$tmpId."
                        and a.delete_flag = 0 and p.delete_flag = 0";
            }else if($type == 4){
                $sql = "select p.*
                        from weixin_area_smotor as a,weixin_transport_price as p
                        where p.area_id = a.area_smotor_id and a.area_smotor_id = ".$id." and p.transport_tmp_id = ".$tmpId."
                        and a.delete_flag = 0 and p.delete_flag = 0";
            }
	  framework_static_function::write_log('运费:'.$sql,'price');
            //var_dump($sql);
            //获取所以运费
            $st = self::DB()->dbh->prepare($sql);
            $st->execute();
            $data = $st->fetchAll();
            $price = 0;
            if($data){
                if(count($data) == 1){
                    foreach($data as $key=>$value){
                        $price = $data[$key]['transport_price'];
                    }
                }else{
                    foreach($data as $key=>$value){
                        if($value['min_weight'] < $number && $number <= $value['max_weight'] ){
                            $price = $data[$key]['transport_price'];
                        }
                    }
                }
            }

        } catch ( Exception $e ) {
            framework_static_function::write_log ( $e->getMessage (), 'weixin_sql' );
            $price = 0;
        }
        return $price;
    }

    public static function getModelPrice($id,$idList){

        try {
            $sql = "select sum(cur_price) as price from weixin_price_attr where produce_id = ".$id." and attr_id in(".$idList.")";
            //echo $sql;
            $st = self::DB()->dbh->prepare($sql);
            $st->execute();
            $data = $st->fetch();
            $price = $data['price'];
        } catch ( Exception $e ) {
            framework_static_function::write_log ( $e->getMessage (), 'weixin_sql' );
            $price = 0;
        }
        return $price;
    }


}
